import sqlite3
from datetime import datetime
from ..utils.db import get_db_connection
from ..utils.logger import get_logger

logger = get_logger(__name__)

class Item:
    """商品模型类"""
    
    def __init__(self, id=None, item_name=None, item_brand=None, item_specs=None, 
                 item_price=None, base_price=None, base_unit=None, item_img=None, 
                 item_classification=None, shop_id=None, shop_name=None, sku_id=None, run_id=None, 
                 created_at=None):
        self.id = id
        self.item_name = item_name
        self.item_brand = item_brand
        self.item_specs = item_specs
        self.item_price = item_price
        self.base_price = base_price
        self.base_unit = base_unit
        self.item_img = item_img
        self.item_classification = item_classification
        self.shop_id = shop_id
        self.shop_name = shop_name
        self.sku_id = sku_id
        self.run_id = run_id
        self.created_at = created_at
    
    @classmethod
    def create_table(cls):
        """创建商品表"""
        conn = get_db_connection()
        conn.execute('''
            CREATE TABLE IF NOT EXISTS item (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                item_name TEXT NOT NULL,
                item_brand TEXT,
                item_specs TEXT,
                item_price REAL,
                base_price REAL,
                base_unit TEXT,
                item_img TEXT,
                item_classification TEXT,
                shop_id INTEGER,
                shop_name TEXT,
                sku_id TEXT UNIQUE,
                run_id TEXT,
                created_at TEXT,
                updated_at TEXT,
                FOREIGN KEY (shop_id) REFERENCES shops (id)
            )
        ''')
        conn.commit()
        conn.close()
    
    @classmethod
    def get_all(cls, page=1, page_size=10, keyword=None, brand=None, shop_id=None):
        """获取所有商品"""
        conn = get_db_connection()
        offset = (page - 1) * page_size
        
        try:
            # 构建查询条件
            conditions = []
            params = []
            
            if keyword:
                conditions.append("item_name LIKE ?")
                params.append(f"%{keyword}%")
            
            if brand:
                conditions.append("item_brand LIKE ?")
                params.append(f"%{brand}%")
            
            if shop_id:
                conditions.append("shop_id = ?")
                params.append(shop_id)
            
            # 构建SQL查询
            where_clause = " AND ".join(conditions) if conditions else "1=1"
            
            # 查询总数
            count_query = f"SELECT COUNT(*) FROM item WHERE {where_clause}"
            try:
                total = conn.execute(count_query, params).fetchone()[0]
            except Exception as e:
                logger.error(f"查询商品总数失败: {str(e)}")
                raise ValueError(f"查询商品总数失败: {str(e)}")
            
            # 查询数据，直接从 item 表获取所有需要的字段，包括 shop_name
            query = f"""
                SELECT * FROM item
                WHERE {where_clause}
                ORDER BY created_at DESC
                LIMIT ? OFFSET ?
            """
            params.extend([page_size, offset])
            
            items = []
            try:
                for row in conn.execute(query, params).fetchall():
                    item = cls(
                        id=row['id'],
                        item_name=row['item_name'],
                        item_brand=row['item_brand'],
                        item_specs=row['item_specs'],
                        item_price=row['item_price'],
                        base_price=row['base_price'],
                        base_unit=row['base_unit'],
                        item_img=row['item_img'],
                        item_classification=row['item_classification'],
                        shop_id=row['shop_id'],
                        shop_name=row['shop_name'],
                        sku_id=row['sku_id'],
                        run_id=row['run_id'],
                        created_at=row['created_at']
                    )
                    items.append(item)
            except Exception as e:
                logger.error(f"查询商品数据失败: {str(e)}")
                raise ValueError(f"查询商品数据失败: {str(e)}")
            
            conn.close()
            return items, total
        except Exception as e:
            conn.close()
            logger.error(f"获取商品列表失败: {str(e)}")
            raise ValueError(f"获取商品列表失败: {str(e)}")
    
    @classmethod
    def get_by_id(cls, item_id):
        """根据ID获取商品"""
        conn = get_db_connection()
        query = """
            SELECT * FROM item
            WHERE id = ?
        """
        row = conn.execute(query, (item_id,)).fetchone()
        conn.close()
        
        if not row:
            return None
        
        return cls(
            id=row['id'],
            item_name=row['item_name'],
            item_brand=row['item_brand'],
            item_specs=row['item_specs'],
            item_price=row['item_price'],
            base_price=row['base_price'],
            base_unit=row['base_unit'],
            item_img=row['item_img'],
            item_classification=row['item_classification'],
            shop_id=row['shop_id'],
            shop_name=row['shop_name'],
            sku_id=row['sku_id'],
            run_id=row['run_id'],
            created_at=row['created_at']
        )
    
    @classmethod
    def get_by_sku(cls, sku_id):
        """根据SKU ID获取商品"""
        conn = get_db_connection()
        query = """
            SELECT * FROM item
            WHERE sku_id = ?
        """
        row = conn.execute(query, (sku_id,)).fetchone()
        conn.close()
        
        if not row:
            return None
        
        return cls(
            id=row['id'],
            item_name=row['item_name'],
            item_brand=row['item_brand'],
            item_specs=row['item_specs'],
            item_price=row['item_price'],
            base_price=row['base_price'],
            base_unit=row['base_unit'],
            item_img=row['item_img'],
            item_classification=row['item_classification'],
            shop_id=row['shop_id'],
            shop_name=row['shop_name'],
            sku_id=row['sku_id'],
            run_id=row['run_id'],
            created_at=row['created_at']
        )
    
    def save(self):
        """保存商品"""
        conn = get_db_connection()
        cursor = conn.cursor()
        
        if self.id is None:
            # 创建新商品
            cursor.execute("""
                INSERT INTO item (
                    item_name, item_brand, item_specs, item_price, 
                    base_price, base_unit, item_img, item_classification,
                    shop_id, shop_name, sku_id, run_id, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                self.item_name, self.item_brand, self.item_specs, self.item_price,
                self.base_price, self.base_unit, self.item_img, self.item_classification,
                self.shop_id, self.shop_name, self.sku_id, self.run_id, self.created_at
            ))
            self.id = cursor.lastrowid
        else:
            # 更新现有商品
            cursor.execute("""
                UPDATE item SET
                    item_name = ?, item_brand = ?, item_specs = ?, item_price = ?,
                    base_price = ?, base_unit = ?, item_img = ?, item_classification = ?,
                    shop_id = ?, shop_name = ?, sku_id = ?, run_id = ?
                WHERE id = ?
            """, (
                self.item_name, self.item_brand, self.item_specs, self.item_price,
                self.base_price, self.base_unit, self.item_img, self.item_classification,
                self.shop_id, self.shop_name, self.sku_id, self.run_id, self.id
            ))
        
        conn.commit()
        conn.close()
        return self.id
    
    def delete(self):
        """删除商品"""
        if self.id is None:
            return False
        
        conn = get_db_connection()
        conn.execute("DELETE FROM item WHERE id = ?", (self.id,))
        conn.commit()
        conn.close()
        return True
    
    @classmethod
    def bulk_delete(cls, ids):
        """批量删除商品"""
        if not ids:
            return 0
        
        conn = get_db_connection()
        placeholders = ','.join(['?'] * len(ids))
        result = conn.execute(f"DELETE FROM item WHERE id IN ({placeholders})", ids)
        deleted_count = result.rowcount
        conn.commit()
        conn.close()
        return deleted_count
    
    @classmethod
    def get_stats(cls):
        """获取商品统计信息"""
        conn = get_db_connection()
        
        # 获取商品总数
        total_items = conn.execute("SELECT COUNT(*) FROM item").fetchone()[0]
        
        # 获取品牌分布
        brands = []
        for row in conn.execute("""
            SELECT item_brand, COUNT(*) as count 
            FROM item 
            WHERE item_brand != '' 
            GROUP BY item_brand 
            ORDER BY count DESC 
            LIMIT 10
        """):
            brands.append({
                'name': row['item_brand'],
                'count': row['count']
            })
        
        # 获取店铺商品分布
        shops = []
        for row in conn.execute("""
            SELECT s.id, s.shop_name, COUNT(i.id) as count 
            FROM shops s
            LEFT JOIN item i ON s.id = i.shop_id
            GROUP BY s.id
            ORDER BY count DESC
            LIMIT 10
        """):
            shops.append({
                'id': row['id'],
                'name': row['shop_name'],
                'count': row['count']
            })
        
        # 获取价格区间分布
        price_ranges = []
        ranges = [
            (0, 10, '0-10元'),
            (10, 50, '10-50元'),
            (50, 100, '50-100元'),
            (100, 500, '100-500元'),
            (500, 1000, '500-1000元'),
            (1000, float('inf'), '1000元以上')
        ]
        
        for start, end, label in ranges:
            if end == float('inf'):
                count = conn.execute(
                    "SELECT COUNT(*) FROM item WHERE item_price >= ?", 
                    (start,)
                ).fetchone()[0]
            else:
                count = conn.execute(
                    "SELECT COUNT(*) FROM item WHERE item_price >= ? AND item_price < ?", 
                    (start, end)
                ).fetchone()[0]
                
            price_ranges.append({
                'range': label,
                'count': count
            })
        
        conn.close()
        
        return {
            'total_items': total_items,
            'brands': {
                'total': len(brands),
                'items': brands
            },
            'shops': {
                'total': len(shops),
                'items': shops
            },
            'price_ranges': price_ranges
        }
    
    def to_dict(self):
        """将对象转换为字典"""
        return {
            'id': self.id,
            'item_name': self.item_name,
            'item_brand': self.item_brand,
            'item_specs': self.item_specs,
            'item_price': self.item_price,
            'base_price': self.base_price,
            'base_unit': self.base_unit,
            'item_img': self.item_img,
            'item_classification': self.item_classification,
            'shop_id': self.shop_id,
            'shop_name': self.shop_name,
            'sku_id': self.sku_id,
            'run_id': self.run_id,
            'created_at': self.created_at,
            'updated_at': self.updated_at
        } 